import json
import os
import sqlite3

# 测试函数search

# 获取version版本1.0时候的项目码
import ipfs_api

from model.models_1 import IPFS
from model.models_3 import Poison


def get_version1_projectCode(version):
    # 连接到 SQLite 数据库
    conn = sqlite3.connect('../users.sqlite')
    cursor = conn.cursor()

    # 执行查询语句
    cursor.execute("SELECT projectCode FROM IPFS WHERE version = ?", (version,))
    result = cursor.fetchone()
    print(result)

    # 关闭数据库连接
    conn.close()
    return result

def get_if_exist(version):
    # 连接到 SQLite 数据库
    conn = sqlite3.connect('../users.sqlite')
    cursor = conn.cursor()

    # 执行查询语句
    cursor.execute("SELECT * FROM IPFS WHERE version = ? ", (version,))
    result = cursor.fetchone()

    # 关闭数据库连接
    conn.close()
    return result

# 毒更新之后，后续全部文件创建都会带上毒，并且标志位设为1
def update_all_ipfs_poison(CID,new_poison):
    try:
        # 连接到数据库
        conn = sqlite3.connect('../users.sqlite')
        cursor = conn.cursor()

        # 查询符合条件的记录中的项目码和毒的名称
        projectCode_poison = cursor.execute("SELECT projectCode, poison FROM IPFS WHERE poisonExist = 1 AND CID = ?",
                                            (CID,)).fetchone()

        # 查询标志位为1的记录，并获取它们的版本号,毒名称
        cursor.execute("SELECT version, poison FROM IPFS WHERE projectCode = ? AND poisonExist = 1 AND CID = ?", (projectCode_poison[0],CID))
        rows = cursor.fetchall()

        # 更新标志位为1的记录之后的版本，并附加上毒的名称
        for row in rows:
            version = row[0]
            existing_poison = row[1]
            print(existing_poison)

            # 如果现有毒名称为空，则直接将新毒名称写入
            if existing_poison is None:
                updated_poison = new_poison
            else:
                # 将现有毒名称和新毒名称以逗号分隔合并，并去重
                poisons = list(set(existing_poison.split(', ') + [new_poison]))
                updated_poison = ', '.join(poisons)

            # 执行更新操作
            cursor.execute(
                "UPDATE IPFS SET poisonExist = 1, poison = ? WHERE projectCode = ? AND version > ?",
                (updated_poison, projectCode_poison[0], version))

        # 提交更改
        conn.commit()
        print("所有在标志位为1的记录之后的版本poison信息已成功更新")

    except sqlite3.Error as e:
        print("发生错误：", e)
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()

def get_cid_from_database1(file_name):
    # 连接到 SQLite 数据库
    conn = sqlite3.connect('../users.sqlite')
    cursor = conn.cursor()

    # 执行查询语句，并对poison字段进行去重
    cursor.execute("SELECT DISTINCT poison ,CID FROM IPFS WHERE filename = ? ORDER BY timestamp", (file_name,))
    result = cursor.fetchall()

    # 关闭数据库连接
    conn.close()
    # 将查询结果转换为字典格式
    formatted_results = []
    # 初始化一个集合，用于保存不同种类的poison
    poison_types = set()

    for row in result:
        poison, cid = row
        formatted_results.append({'CID': cid, 'poison': poison})
        if poison:
            poison_types.add(poison)

    all_poisons_str = ', '.join(poison_types)
    print(all_poisons_str)
    input_list = all_poisons_str.split(',')  # 将字符串拆分成列表
    print(input_list)
    # 去除首尾空格并统一格式
    cleaned_data = [item.strip().replace(' ', '') for item in input_list]

    unique_list = list(set(cleaned_data))  # 将列表转换为集合去重，再转换为列表
    print(unique_list)
    output_str = ','.join(unique_list)  # 将去重后的列表转换为字符串
    print(output_str)
    return formatted_results, output_str


def update_ipfs_poison(cid):
    try:
        # 连接到数据库
        conn = sqlite3.connect('../users.sqlite')
        cursor = conn.cursor()

        # 查询指定 CID 的上游 CIDS
        cursor.execute("SELECT upCIDS, poison FROM IPFS WHERE CID = ?", (cid,))
        row = cursor.fetchone()
        up_cids = row[0]
        print(up_cids)
        poison_Num = row[1]
        # 默认无毒
        real_poisonExist = 0

        if up_cids:
            # 分割上游 CIDS
            up_cids_list = up_cids.split(',')

            updated_poison_list = []  # 用于累积所有查询得到的 poison
            print(up_cids_list)
            for up_cid in up_cids_list:
                # 查询上游 CID 在毒表中的毒信息
                print(up_cid)
                cursor.execute("SELECT poison FROM poison WHERE CID = ?", (up_cid,))
                row = cursor.fetchone()
                # 检查是否找到了符合条件的记录
                if row is not None:
                    print("r",row[0])
                    real_poisonExist = 1
                    existing_poison = row[0]

                    # 如果现有毒字段不为空，则添加到累积列表中
                    if existing_poison:
                        updated_poison_list.append(existing_poison)

            # 使用逗号拼接所有毒信息
            updated_poison = ','.join(updated_poison_list)
            print(updated_poison)

        if poison_Num:
            real_poison = updated_poison
        else:
            real_poison = "," + updated_poison

        print(real_poison)
        # 更新当前 CID 的毒信息和毒存在标记
        cursor.execute("UPDATE IPFS SET poisonExist = ?, poison = ? WHERE CID = ?",
                                   (real_poisonExist, real_poison, cid))

        # 提交更改
        conn.commit()
        print("毒信息已成功更新到IPFS表和Poison表中")

    except sqlite3.Error as e:
        print("发生错误：", e)
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()

# 检查一级毒源
def check_poison_origin(poison):
    if poison is None or poison.strip() == '':  # 如果 poison 为 None 或者为空字符串
        return "暂无"

    # 以逗号为分隔符拆分 poison
    poisons = poison.split(', ')
    cids = []
    conn = sqlite3.connect('../users.sqlite')
    cursor = conn.cursor()

    # 查询数据库，找到每个 poison 对应的 CID
    for poi in poisons:
        cursor.execute("SELECT CID FROM poison WHERE poison = ?", (poi,))
        result = cursor.fetchone()
        if result and result[0]:  # 判断查询结果不为空且 CID 不为空
            cids.append(str(result[0]))

    conn.close()

    if len(cids) == 1:
        return cids[0]
    else:
        return ','.join(cids)  # 将 CID 列表转换为逗号分隔的字符串并返回


# 从IPFS中获取同名文件的CID和poison
def get_cid_from_database(up_CIDS):
    # 连接到 SQLite 数据库
    conn = sqlite3.connect('../users.sqlite')
    cursor = conn.cursor()

    # 将查询结果转换为字典格式
    formatted_results = []

    # 初始化一个集合，用于保存不同种类的poison
    poison_types = set()

    # 按逗号分割 up_CIDS
    cids_list = up_CIDS.split(',')

    # 遍历 up_CIDS 中的每个 CID
    for cid in cids_list:

        # 执行查询语句，获取给定文件名和 CID 对应的毒属性
        cursor.execute("SELECT DISTINCT poison FROM IPFS WHERE CID = ?", (cid,))
        result = cursor.fetchall()

        # 提取毒属性并添加到 poison_types 集合中
        for row in result:
            poison = row[0]
            formatted_results.append({'CID': cid, 'poison': poison})
            if poison:
                poison_types.add(poison)

    # 关闭数据库连接
    conn.close()

    all_poisons_str = ', '.join(poison_types)
    input_list = all_poisons_str.split(',')
    cleaned_data = [item.strip().replace(' ', '') for item in input_list]
    unique_list = list(set(cleaned_data))
    output_str = ','.join(unique_list)

    return formatted_results, output_str


def download_file1():
    # 从请求参数中获取 CID

    filename ="原神默认字体 - Genshin default fonts.ttf"
    true_filename, file_extension = os.path.splitext(filename)
    # 从文件名中提取出真正的文件名
    userid = 5
    version = 1.0
    # 构建文件路径
    file_path = f"uploads/{true_filename}_user{userid}_{version}{file_extension}"
    print(filename)
    print(true_filename)
    print(file_path)

def download_file(file_cid: str, path: str):
    ipfs_api.download(file_cid, path)

# 从IPFS中获取同名文件的CID和poison
def get_cid_from_database1(up_CIDS):
    # 连接到 SQLite 数据库
    conn = sqlite3.connect('../users.sqlite')
    cursor = conn.cursor()

    # 将查询结果转换为字典格式
    formatted_results = []

    # 初始化一个集合，用于保存不同种类的poison
    poison_types = set()

    # 按逗号分割 up_CIDS
    cids_list = up_CIDS.split(',')

    # 遍历 up_CIDS 中的每个 CID
    for cid in cids_list:
        # 执行查询语句，获取给定文件名和 CID 对应的毒属性
        cursor.execute("SELECT DISTINCT poison FROM IPFS WHERE CID = ?", (cid,))
        result = cursor.fetchall()

        # 提取毒属性并添加到 poison_types 集合中
        for row in result:
            poison = row[0]
            if poison is None:
                poison = '[]'  # 将 None 转换为一个空的字符串数组
            formatted_results.append({'CID': cid, 'poison': poison})
            if poison:
                poison_types.add(poison)

    # 关闭数据库连接
    conn.close()

    all_poisons_str = ', '.join(poison_types)
    input_list = all_poisons_str.split(',')
    cleaned_data = [item.strip().replace(' ', '') for item in input_list]
    unique_list = list(set(cleaned_data))
    output_str = ','.join(unique_list)

    return formatted_results, output_str

if __name__ == "__main__":
    # ss = get_if_exist('1.0')
    # print(ss)

    # st = get_version1_projectCode('1.0')
    # print("s %s" % 1.0)
    # print(st[0])

    # update_all_ipfs_poison('QmRLv9cduKFV6F71trMMwKcAZrAkPynFK2iFjGo6eZyZxQ','adas')

    # s,t = get_cid_from_database("新建文本文档.txt")
    # print(s)
    # print(t)
    # a = ['12312', 'sdfsdfsdf']
    # print(a[0])

    # up_CIDS = ['QmdTbu49G7oavJepc3NKFhjXEhCjEhSKi4CR7MKanMBxUJ','QmQJ1nVkRT8io6P2Vw8Ecjp9D8LNVxHB47i61uzzZtjc7z']
    # q,e = check_poison_exist(up_CIDS)
    # print(q)
    # print(e)

    # print(check_poison_origin("bbuutt,dadas"))

    # update_ipfs_poison("QmYrKgM6UAHooW5dB1ennYbnbiN3DQRLfnrUmFKkeYVseM",1,"12")

    # updated_poisons = "123123123"
    # updated_poison_str = ","+updated_poisons
    # print(updated_poison_str)

    # update_ipfs_poison("QmNSN1BdFekKWCuMpxnJ78ye6VY94PKbLcEyAuCPC7wupE")

    # a = check_poison_origin("ppp, happy")
    # print(a)

    # a,b = get_cid_from_database("QmUyDkTtA39ZfrBDGg6sV9KxdfHGrQeUxgucrKW445xKjv,QmUERojAjXWpCwLiwgXAxHodtyXHJbtje2h5mzX1ZBSmSp")
    # print(a)
    # print(b)

    # download_file()
    # download_file("QmRpuW2fMe3ptoT9xsmm29w6vqcUdSghwbLaqjH8Aus2nZ",r"C:\Users\86136\Desktop\大三下\金融安全\期末大作业\新建文件夹\SSSM\uploads\test_user5_2.2.txt")

    a= get_cid_from_database1("2312312")
    print(a[0])
    print(a[1])